/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.servico;

import camada.bancodedados.conexao.Conexao;
import camada.bancodedados.daoimplemente.cliente.PessoaFisicaDAOImpl;
import camada.negocio.dao.servico.ServicoDAO;
import eloma.classe.Cliente;
import eloma.classe.servico.Servico;
import eloma.classe.servico.ServicoCategoria;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author ICARO KLINGER
 */
public class ServicoDAOImpl implements ServicoDAO {

    PessoaFisicaDAOImpl daoCliente = new PessoaFisicaDAOImpl();

    //<editor-fold desc="Metodos Basicos">
    @Override
    //<editor-fold desc="Metodo Inserir Servico">
    public void inserirServico(Servico servico) {
        PreparedStatement pstm = null;
        try {
            final String SQL_INSERIR_SERVICO
                    = "INSERT INTO SERVICO(IDEMPRESA,IDCLIENTE,DATA,IDSERVICO_CATEGORIA,DESCRICAO,STATUS,AVALIACAO)values(?,?,?,?,?,?,?)";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_INSERIR_SERVICO);
            pstm.setInt(1, servico.getIdEmpresa());
            pstm.setInt(2, servico.getCliente().getIdCliente());
            java.sql.Date dataSql = new java.sql.Date(servico.getDataServico().getDate());
            pstm.setDate(3, dataSql);
            pstm.setInt(4, servico.getServicoCategoria().getIdServicoCategoria());
            pstm.setString(5, servico.getDecricao());
            pstm.setInt(6, servico.getStatus());
            pstm.setString(7, servico.getAvaliacao());
            pstm.executeUpdate();

        } catch (SQLException e) {
            String w = e.getMessage();
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="Metodo Atualizar Servico">
    public void atualizarServico(Servico servico) {
        PreparedStatement pstm = null;
        try {
            final String SQL_UPDATE
                    = "UPDATE SERVICO SET IDEMPRESA = ? ,IDCLIENTE = ? ,DATA = ? ,IDSERVICO_CATEGORIA = ? ,DESCRICAO = ? ,STATUS = ? ,AVALIACAO = ? WHERE IDSERVICO = ? ";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_UPDATE);
            pstm.setInt(1, servico.getIdEmpresa());
            pstm.setInt(2, servico.getCliente().getIdCliente());
            java.sql.Date dataSql = new java.sql.Date(servico.getDataServico().getDate());
            pstm.setDate(3, dataSql);
            pstm.setInt(4, servico.getServicoCategoria().getIdServicoCategoria());
            pstm.setString(5, servico.getDecricao());
            pstm.setInt(6, servico.getStatus());
            pstm.setString(7, servico.getAvaliacao());
            pstm.executeUpdate();
        } catch (SQLException e) {
        }
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="Metodo Remover Servico">
    public void removerServico(int idServico) {
        PreparedStatement pstm = null;
        try {
            final String SQL_DELETE = "DELETE FROM SERVICO WHERE IDSERVICO = ?";
            pstm = (PreparedStatement) Conexao.getCon().prepareStatement(SQL_DELETE);
            pstm.setInt(1, idServico);
            pstm.executeUpdate();
        } catch (SQLException e) {
        }
    }
    //</editor-fold>

    //</editor-fold>
    //<editor-fold desc="Metodos de Pesquisa">
    @Override
    //<editor-fold desc="Metodo Selecionar Todos os Servicos">
    public List<Servico> selecionarServico() throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "SELECT * FROM SERVICO";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            rs = stmt.executeQuery();

        } catch (SQLException sqle) {
        }

        return carregarVariosResultados(rs);
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="Metodo Selecionar Servicos por Identificador">
    public Servico selecionarServicoPorId(int idServico) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "SELECT * FROM SERVICO WHERE IDSERVICO = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, idServico);
            rs = stmt.executeQuery();

        } catch (Exception ex) {
        }

        return carregarSimpleResultado(rs);
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="Metodo Selecionar Todos os Servicos Categoria">
    public List<ServicoCategoria> selecionarServicoCategoria() throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "SELECT * FROM SERVICO_CATEGORIA";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            rs = stmt.executeQuery();

        } catch (SQLException sqle) {
        }

        return carregarVariosResultadosCategoria(rs);
    }
    //</editor-fold>

    @Override
    //<editor-fold desc="Metodo Selecionar Servicos Categoria por Identificador">
    public ServicoCategoria selecionarServicoCategoriaPorId(int idServicoCategoria) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String SELECT_SAIDACHEQUE = "SELECT * FROM SERVICO_CATEGORIA WHERE SERVICO_CATEGORIA = ?";
        try {
            stmt = (PreparedStatement) Conexao.getCon().prepareStatement(SELECT_SAIDACHEQUE);
            stmt.setInt(1, idServicoCategoria);
            rs = stmt.executeQuery();

        } catch (Exception ex) {
        }

        return carregarSimpleResultadoCategoria(rs);
    }
    //</editor-fold>

    //</editor-fold>
    //<editor-fold desc="Metodos Auxiliares">
    public List<Servico> carregarVariosResultados(ResultSet rs) throws SQLException {
        List<Servico> resultList = new ArrayList<Servico>();
        while (rs.next()) {
            Servico dto = new Servico();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    public List<ServicoCategoria> carregarVariosResultadosCategoria(ResultSet rs) throws SQLException {
        List<ServicoCategoria> resultList = new ArrayList<ServicoCategoria>();
        while (rs.next()) {
            ServicoCategoria dto = new ServicoCategoria();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    private ServicoCategoria carregarSimpleResultadoCategoria(ResultSet rs) throws SQLException {
        if (rs.next()) {
            ServicoCategoria dto = new ServicoCategoria();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    private Servico carregarSimpleResultado(ResultSet rs) throws SQLException {
        if (rs.next()) {
            Servico dto = new Servico();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    private void carregarVO(Servico dto, ResultSet rs) throws SQLException {
        try {
            dto.setIdServico(rs.getInt("IDSERVICO"));
            dto.setStatus(rs.getInt("idEmpresa"));
            Cliente cliente = daoCliente.buscaId(rs.getInt("IDCLIENTE"));
            dto.setCliente(cliente);
            dto.setDataServico(rs.getDate("DATA"));
            ServicoCategoria servico = this.selecionarServicoCategoriaPorId(rs.findColumn("idServico_Categoria"));
            dto.setServicoCategoria(servico);
            dto.setDecricao(rs.getString("DESCRICAO"));
            dto.setStatus(rs.getInt("STATUS"));
            dto.setAvaliacao(rs.getString("AVALIACAO"));
            dto.setSituacao(rs.getInt("SITUACAO"));
        } catch (Exception e) {
        }
    }

    private void carregarVO(ServicoCategoria dto, ResultSet rs) throws SQLException {
        dto.setIdServicoCategoria(rs.getInt("IDSERVICO_CATEGORIA"));
        dto.setDescricao(rs.getString("DESCRICAO"));
    }
    //</editor-fold>
}
